package com.wy.util.HRBU;

/**
 * Created by wy on 2018/2/26.
 * 哈工程日程中心
 *
 * 最后转换为insert语句.这一把要优化
 */

import com.wy.campus.Room;
import com.wy.campus.model.hrbuModel.HrbuStudyRoom;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

public class HrbuGTD {

    //校历
    private static String xl = "SELECT * FROM t_schooltime WHERE xn='2017-2018' and xq=1 and schoolid=193";
    //获取教室使用情况
    private static String useroom = "select * from " +
            "(select rownum as rowno,room.* from \"JWXT\".\"V_MH_STUDYROOM_APP\" room where xn = '2017-2018' and xq = '1' and roomname is not null) t where t.rowno>=? and t.rowno<=?";

    //获取课程信息
    private static String getCourse = "select xh,kcmc,skzc,skxq,skjc,cxjc,jxlh,jxlm,jsh from t_coursebksxkxs where schoolid = 193";

    public static void main(String[] args) {
        HrbuGTD hrbuGTD = new HrbuGTD();
        hrbuGTD.execute();
    }


    public void execute(){
        Map<String,Room> rooms = new HashMap<>();

        Connection courseConn = null;//获取课程的数据库连接
        File file=new File("哈工程课程导入日程.sql");
        FileWriter fw = null;
        try {
            System.out.println("开始获取校历");
            Map<String, List<Date>> schoolTime = getXlTime();//获得校历时间
            System.out.println("获取成功");

            fw = new FileWriter(file);

            System.out.println("开始尝试连接mysql数据库！");
            courseConn = getConn();
            System.out.println("连接成功");

            getRoom(courseConn, useroom, rooms, schoolTime);
            System.out.println("rooms数据长度:" + rooms.size());
            StringBuilder temp = new StringBuilder();
            for (Room room : rooms.values()) {
                String roomSql = roomInsertSqlProudce(room);
                temp.append(roomSql);
            }
            fw.append(temp);
            System.out.println("sql长度："+temp.length());

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
                // 注意关闭的顺序，最后使用的最先关闭
                if (courseConn != null){
                    courseConn.close();
                }
                System.out.println("数据库连接已关闭！");
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                fw.flush();
                fw.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }
    //将course转化为日程
    public void getRoom(Connection conn, String sql, Map<String,Room> rooms, Map<String, List<Date>> schoolTime){
        PreparedStatement ps = null;
        ResultSet rs = null;
        String countSql = "SELECT count(*) as num FROM \"JWXT\".\"V_MH_STUDYROOM_APP\" where xn = '2017-2018' and xq = '1'";
        try {
            ps = conn.prepareStatement(countSql);
            rs = ps.executeQuery();
            rs.next();
            int totalPage = rs.getInt("num")/5000+1;
            System.out.println("数据总页数:" + totalPage);
            rs.close();//关闭查询结果和statement，以免因为复用造成内存泄露
            ps.close();
            for(int page=0;page<totalPage;page++) {
                System.out.println("第几页：" + page);
                List<HrbuStudyRoom> studyrooms = new ArrayList<>();
                ps = conn.prepareStatement(useroom);
                ps.setInt(1,page * 5000);
                ps.setInt(2, 5000);
                rs = ps.executeQuery();
                while (rs.next()){
                    HrbuStudyRoom hrbuStudyRoom = new HrbuStudyRoom();
                    hrbuStudyRoom.setRoomType(rs.getString("roomtype"));
                    hrbuStudyRoom.setRoomName(rs.getString("roomname"));
                    hrbuStudyRoom.setDivisionid(rs.getString("divisionid"));
                    hrbuStudyRoom.setDivisionName(rs.getString("divisionName"));
                    hrbuStudyRoom.setHasSeat(rs.getString("hasseat"));
                    hrbuStudyRoom.setExamSeat(rs.getString("examseat"));
                    hrbuStudyRoom.setBuildingName(rs.getString("buildingname"));
                    hrbuStudyRoom.setXn(rs.getString("xn"));
                    hrbuStudyRoom.setXq(rs.getString("xq"));
                    hrbuStudyRoom.setKkzc(rs.getString("kkzc"));
                    hrbuStudyRoom.setKcsj(rs.getString("kcsj"));
                    studyrooms.add(hrbuStudyRoom);
                }
                //循环周次
                for (String zc : schoolTime.keySet()){
                    List<Date> dates = schoolTime.get(zc);//获取某一周的七天日期
                    String zcToInt = zc.replace("zc_", "");  //这里又将getTime的key替换,原本的为 "zc_1"表示第1周,转变后变为1
                    int currentWeek = Integer.valueOf(zcToInt);
                    //遍历一周七天,即在这里处理整个学期某一天
                    for (int i = 1; i <= 7; i++){
                        /**获得当天日期*/
                        Date date = dates.get(i - 1);//获得每一天的日期
                        String strFormat = "yyyy-MM-dd";
                        String useDate = null;
                        SimpleDateFormat dateFormat = new SimpleDateFormat(strFormat);
                        if (date != null) {
                            useDate = dateFormat.format(date);
                        }
                        /**获取当天日期结束*/
                        //遍历房间使用情况
                        for (HrbuStudyRoom hrbuStudyRoom : studyrooms){
                            //该教室的使用周次
                            String[] skzc = hrbuStudyRoom.getKkzc().split(",");
                            //该教室的使用星期和节次
                            String[] kcsj = hrbuStudyRoom.getKcsj().split(",");
                            int xq = Integer.valueOf(kcsj[1]) / 100;//上课星期
                            String[] jc = hrbuStudyRoom.getKcsj().split("," + xq);//上课节次
                            //遍历每个教室的上课周次
                            for (int j = 1; j < skzc.length; j++){
                                if (skzc[j].length() < 0 || skzc[j] == null || skzc[j].equals("")){
                                    continue;
                                }
                                if (Integer.valueOf(skzc[j]) == currentWeek && xq == i){
                                    Room room;
                                    //以使用日期+教室名称作为key,
                                    if(rooms.containsKey(useDate+hrbuStudyRoom.getRoomName())) {
                                        room = rooms.get(useDate+hrbuStudyRoom.getRoomName());
                                    }else{
                                        room = new Room();
                                        room.setClazz(new String[13]);
                                        rooms.put(useDate+hrbuStudyRoom.getRoomName(), room);//将room放入Map中
                                        room.setDivisionName("本部");
                                        room.setBuildingName(hrbuStudyRoom.getBuildingName());
                                        room.setDivisionId(hrbuStudyRoom.getDivisionid());
                                        room.setRoomName(hrbuStudyRoom.getRoomName());
                                        room.setRoomtype(hrbuStudyRoom.getRoomType());
                                        room.setSchoolid(193);
                                        room.setUseDate(useDate);
                                        room.setXn(hrbuStudyRoom.getXn());
                                        room.setXq(Integer.valueOf(hrbuStudyRoom.getXq()));
                                        room.setHasSeat(hrbuStudyRoom.getHasSeat());
                                        room.setExamSeat(hrbuStudyRoom.getExamSeat());
                                    }
                                    for(String kcjc : jc){
                                        if (kcjc != null && kcjc.length() > 0 && !kcjc.equals("")){
                                            kcjc = kcjc.replaceAll(",","");
                                            room.getClazz()[Integer.valueOf(kcjc) - 1] = "占用";
                                        }
                                    }
                                }
                            }
                        }
                    }

                }

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (rs != null)
                    rs.close();
                if (ps != null)
                    ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    //将Room对象转换为SQL语句
    private String roomInsertSqlProudce(Room room) {

        StringBuilder insertSql = new StringBuilder();
        insertSql.append("INSERT INTO `mobilecampus`.`t_room` ( `roomname`,`roomtype`,`xn`, `xq`,`schoolid`,`usedate`,`divisionname`,`divisionid`,`buildingname`,`hasseat`,`examseat`");

        for(int i=1;i<13;i++) {
            insertSql.append(",class"+i);
        }
        //TODO 注意每次这里修改学校ID
        insertSql.append(String.format(") VALUES('%s','%s','%s',%d,193,'%s','%s','%s','%s','%s','%s'",room.getRoomName(),room.getRoomtype(),room.getXn(),room.getXq(),
                room.getUseDate(),room.getDivisionName(),room.getDivisionId(),room.getBuildingName()==null ? "" : room.getBuildingName(),room.getHasSeat(),room.getExamSeat()
        ));

        for(int i=0;i<12;i++) {
            insertSql.append(String.format(",'%s'",room.getClazz()[i]==null?"":room.getClazz()[i]));
        }
        insertSql.append(");\n");
        return insertSql.toString();
    }

    public Map<String, List<Date>> getXlTime(){
        Connection conn = null;
        String className = "com.mysql.jdbc.Driver";
        String url ="jdbc:mysql://192.168.254.162:3306/mobilecampus";
        String username ="lantu";
        String password ="Lantu35791";
        try {
            Class.forName(className);
            try {
                conn = DriverManager.getConnection(url, username, password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Map<String, List<Date>> schoolTime = null;
        PreparedStatement ps = null;
        try {
            schoolTime = getSchoolTime(xl, conn, ps);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if (ps != null)
                    ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return schoolTime;
    }
    public Map<String, List<Date>> getSchoolTime(String sql, Connection conn, PreparedStatement pst) {
        //List<SchoolTime> list = schoolTimeRedisDAO.getSchoolTime("2016-2017", 2, 151);
        Map<String, List<Date>> map = new HashMap<>();
        ResultSet rs = null;
        try{
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();


            while (rs.next()) {
                int type = rs.getInt("type");
                int xlzc = rs.getInt("zc");
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String starttime = rs.getString("starttime");
                Date startDate=formatter.parse(starttime);
                if (type == 1) {
                    String zc = "zc_" +xlzc;
                    List<Date> date = new ArrayList<>();

                    Calendar calendar = Calendar.getInstance();
                    // System.out.println(zc);
                    for (int i = 0; i < 7; i++) {
                        calendar.setTime(startDate);
                        calendar.add(Calendar.DAY_OF_MONTH, i);
                        date.add(calendar.getTime());
                        // System.out.println(calendar.getTime());
                    }
                    map.put(zc, date);
                }

            }

        }catch(Exception e){
            System.out.println("date is error");
        }finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return map;
    }
    private Connection getConn(){
        Connection conn = null;
        String className = "com.mysql.jdbc.Driver";
        // oracleName: oracle.jdbc.driver.OracleDriver
        String url ="jdbc:mysql://192.168.254.162:3306/mobilecampus";
        String username ="lantu";
        String password ="Lantu35791";
        try {
            Class.forName(className);
            try {
                conn = DriverManager.getConnection(url, username, password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }

}

